"""
两张表数据的全比对 查询出修改了的数据 然后将更改的数据写入新表的状态位
"""
import operator
import traceback

from xjlibrary.database_moudel.sqlmoudle import AbsDatabase
from xjlibrary.database_moudel.sqlmoudle.sqlbuilder import Sqlite3Builder
from xjlibrary.our_file_dir import BaseDir

curpath = BaseDir.get_file_dir_absolute(__file__)
# 配置文件路径
dbpath = BaseDir.get_new_path(curpath, "db.ini")
print(dbpath)


# db3数据的连接返回一个连接对象
def conn(sec):
    """
    :param sec:  section
    :return:
    """
    sqllite3 = AbsDatabase.get_sql_factory().sqlite_factory()
    # 传入的是配置文件和section选项 dbpath为配置文件路径
    sqlite3_moudle = Sqlite3Builder(dbpath, sec).build_all().get_moudle()
    print(sqlite3_moudle.to_dict())
    sqllite3.link(sqlite3_moudle)
    # 设置txt的编码格式  hadoop 上的db3为gbk 默认为utf8
    sqllite3.set_encoding(encoding="gbk")
    # 返回一个连接
    return sqllite3


def select_db3_data(newconn, sql):
    print(sql)
    result = newconn.execute(sql)
    result = result.fetchall()
    return result


def data_to_dicts(result, dicts1):
    for row in result:
        dicts1[row[0]] = row


def get_new_data(newconn, offset):
    # 新表中获取数据
    sql = "select * from `modify_title_info_zt` limit {},10000".format(offset)
    result = select_db3_data(newconn, sql)
    return result


def main():
    newconn = conn("sqlite3new")
    oldconn = conn("sqlite3old")
    offset = 0
    listall = []
    whileBool = True
    while whileBool:
        dicts1 = {}
        dicts2 = {}
        result = get_new_data(newconn, offset)
        if len(result) < 10000:
            whileBool = False
        # 存入dict1
        data_to_dicts(result, dicts1)
        keys = dicts1.keys()
        # 获取old表中相同键的数据
        sql = "select * from `modify_title_info_zt` where lngid in {}".format(tuple(keys))
        result1 = select_db3_data(oldconn, sql)
        # 放入dicts2
        data_to_dicts(result1, dicts2)
        # 取出key
        for key in dicts1:
            try:
                # 得到对应的值 值是键值对
                rows1 = dicts1[key]
                rows2 = dicts2[key]
                # 比对两个数据
                bool_result = operator.eq(rows1[:-1], rows2[:-1])
                if not bool_result:
                    # 将不一样的数据提取出来
                    listall.append(key)
            except:
                print(traceback.format_exc())
                listall.append(key)
        # 更新new表状态
        sql = "update `modify_title_info_zt` set batch='1' where lngid in {}".format(tuple(listall))
        print(sql)
        print(len(listall))
        newconn.execute(sql)
        newconn.my_commit()
        listall.clear()
        offset += 10000

    sql = "update `modify_title_info_zt` set batch='1' where lngid in {}".format(tuple(listall))
    print(len(listall))
    newconn.execute(sql)
    newconn.my_commit()
